Practicing Power BI and DAX Interview Questions – Complete Detailed Notes

Course: Microsoft Power BI Training

Topic: Practicing Power BI and DAX Interview Questions

Course Link: Microsoft Power BI Training – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Power BI and DAX Interview Preparation

Power BI is a business intelligence and data visualization platform used to connect, transform, model, analyze, and present data through interactive reports and dashboards. DAX (Data Analysis Expressions) is the formula language used in Power BI to create measures, calculated columns, calculated tables, and analytical calculations.

Power BI interviews commonly evaluate practical knowledge rather than definitions alone. Interviewers may ask candidates to explain data preparation, relationships, star schemas, filter context, DAX functions, report design, performance optimization, publishing, security, and real-world problem-solving.

Effective interview preparation combines conceptual understanding, hands-on practice, scenario-based questions, and the ability to explain why a particular solution is appropriate.

1.1 Learning Objectives

• Understand important Power BI concepts and architecture.
• Explain Power Query, data modeling, relationships, and star schemas.
• Differentiate calculated columns, measures, and calculated tables.
• Understand row context, filter context, and context transition.
• Practice frequently asked DAX interview questions.
• Write DAX formulas for business KPIs, time intelligence, ranking, and comparisons.
• Prepare for scenario-based questions and troubleshooting tasks.
• Explain report performance, Power BI Service, refresh, and Row-Level Security.
• Present a Power BI project confidently during an interview.

2. Understanding Power BI Fundamentals

2.1 What Is Power BI?

Power BI is a Microsoft business intelligence platform that helps users transform raw data into meaningful insights. It supports data connectivity, data preparation, semantic modeling, calculations, visualization, sharing, and report consumption.

2.2 Main Components of Power BI

2.3 Common Power BI Terminology

• Report: A collection of interactive pages containing visualizations and filters.
• Dashboard: A single-page canvas in the Power BI Service that can contain pinned tiles.
• Semantic model: The organized data and calculation layer used by reports.
• Visual: A chart, table, card, map, or another way of displaying data.
• Slicer: A visual control that lets users filter report data.
• Measure: A DAX calculation evaluated according to the current filter context.
• Workspace: A collaborative area for managing Power BI content.
• Refresh: The process of updating model data from its source.

Interview Question 1: What is Power BI?

Sample Answer: Power BI is a business intelligence platform used to connect to different data sources, transform data, create relationships and calculations, and build interactive reports. It enables users to analyze KPIs, identify trends, and share insights with business stakeholders.

Interview Question 2: What is the difference between a report and a dashboard?

3. Data Sources and Power Query

3.1 What Is Power Query?

Power Query is the data preparation and transformation engine used in Power BI. It allows users to connect to data sources, clean inconsistent values, change data types, merge tables, append queries, remove duplicates, and reshape data before loading it into the semantic model.

Power Query uses the M language. Its transformations are generally executed during data preparation or refresh rather than as interactive DAX calculations in report visuals.

3.2 Common Power Query Operations

• Remove unnecessary columns and rows.
• Replace null values and standardize text.
• Change data types and locale settings.
• Split or merge columns.
• Group rows and aggregate values.
• Merge queries using matching keys.
• Append queries with compatible structures.
• Pivot and unpivot columns.
• Create conditional and custom columns.
• Filter data early where appropriate to reduce unnecessary processing.

3.3 Merge vs. Append in Power Query

Interview Question 3: What is query folding?

Sample Answer: Query folding occurs when Power Query translates supported transformation steps into a query that the source system can execute. For example, filtering a SQL table may be translated into a SQL WHERE clause. This can improve performance by reducing the amount of data transferred to Power BI. Query folding depends on the connector, source, and transformations used.

Interview Question 4: What is the difference between Import and DirectQuery?

3.4 Practical Power Query Example

Suppose a sales file contains inconsistent product names, blank order IDs, and an incorrectly typed sales amount. A reasonable transformation process is to remove invalid records, standardize text, set the sales amount to a numeric type, and verify the results before loading the data.

let
    Source = Excel.Workbook(
        File.Contents("C:\Data\Sales.xlsx"),
        null,
        true
    ),
    SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(
        SalesSheet,
        [PromoteAllScalars=true]
    ),
    ChangedTypes = Table.TransformColumnTypes(
        PromotedHeaders,
        {
            {"OrderID", type text},
            {"SalesAmount", Currency.Type}
        }
    ),
    CleanProductNames = Table.TransformColumns(
        ChangedTypes,
        {
            {"Product", each Text.Trim(Text.Clean(_)), type text}
        }
    ),
    ValidOrders = Table.SelectRows(
        CleanProductNames,
        each [OrderID] <> null and [OrderID] <> ""
    )
in
    ValidOrders

Interview Tip: Explain that the file path, worksheet name, and column names must match the actual source. Also mention that null handling should be appropriate for the business requirement.

4. Data Modeling and Relationships

4.1 What Is Data Modeling?

Data modeling organizes data into related tables so that calculations and reports behave correctly and efficiently. A good model defines the grain of each table, uses suitable relationships, avoids unnecessary duplication, and separates descriptive attributes from business transactions where appropriate.

4.2 What Is a Star Schema?

A star schema consists of a central fact table connected to dimension tables. The fact table contains business events or measurements, while dimension tables describe entities such as products, customers, dates, and regions.

4.3 Why Is a Star Schema Recommended?

• It creates a clear distinction between facts and descriptive dimensions.
• It generally simplifies DAX calculations.
• It makes relationships easier to understand.
• It supports consistent filtering and aggregation.
• It can improve report usability and model maintainability.

4.4 Relationship Cardinality

Interview Question 5: What is the difference between a fact table and a dimension table?

Sample Answer: A fact table records business events and numeric values, such as quantity, revenue, or cost. A dimension table stores descriptive attributes, such as product name, customer segment, date, or region. Dimension tables are commonly used to filter and group fact data.

Interview Question 6: What is relationship cardinality?

Sample Answer: Cardinality describes how rows in one table relate to rows in another table. Power BI supports one-to-many, one-to-one, and many-to-many relationships. I generally prefer one-to-many relationships from unique dimension keys to fact-table foreign keys when the business model supports them.

Interview Question 7: What is the difference between single-direction and bidirectional filtering?

Sample Answer: Single-direction filtering allows filters to flow in one specified direction across a relationship. Bidirectional filtering allows filters to flow in both directions. Bidirectional filtering can be useful for certain models but may introduce ambiguity or unexpected results, so I use it only when the model requires it.

4.5 Active and Inactive Relationships

An active relationship is used by default for filter propagation. An inactive relationship is not used automatically but can be activated for a particular calculation with the DAX function USERELATIONSHIP, when the model and expression support it.

Sales by Delivery Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        FactSales[DeliveryDate],
        DimDate[Date]
    )
)

This example assumes a valid relationship exists between the delivery date and the date dimension, and that the measure uses the intended model context.

5. DAX Fundamentals

5.1 What Is DAX?

DAX stands for Data Analysis Expressions. It is a formula language used in Power BI, Analysis Services tabular models, and related Microsoft analytical tools. DAX supports aggregation, filtering, logical tests, date calculations, ranking, table expressions, and context-aware calculations.

5.2 Main DAX Object Types

Interview Question 8: What is the difference between a measure and a calculated column?

Sample Answer: A calculated column evaluates an expression for each row and stores the resulting values in the model. A measure is evaluated at query time according to the current filter context. I use calculated columns for row-level attributes needed for grouping or relationships, and measures for aggregations and KPIs that must respond to report filters.

5.3 Basic DAX Measures

Assume a table named FactSales with columns SalesAmount, CostAmount, and Quantity.

Total Sales =
SUM(FactSales[SalesAmount])

Total Cost =
SUM(FactSales[CostAmount])

Total Quantity =
SUM(FactSales[Quantity])

Total Profit =
[Total Sales] - [Total Cost]

Profit Margin =
DIVIDE([Total Profit], [Total Sales], 0)

Explanation: SUM adds numeric values in the current context. DIVIDE handles division by zero or blank denominators more safely than a simple division expression when an alternate result is supplied.

5.4 Calculated Column Example

Line Amount =
FactSales[Quantity] * FactSales[UnitPrice]

This calculated column evaluates the multiplication for each row. If the requirement is only to calculate the overall sales amount, a measure may be more appropriate, depending on the business definition and data grain.

6. Row Context and Filter Context

6.1 What Is Row Context?

Row context means DAX is evaluating an expression for a particular row. Calculated columns naturally have row context. Iterator functions such as SUMX also create row context for each row of the table expression they iterate over.

6.2 What Is Filter Context?

Filter context is the set of filters affecting a calculation. It can come from slicers, visual axes, report filters, page filters, relationships, and DAX expressions that modify filters.

6.3 What Is Context Transition?

Context transition is the process by which row context is converted into filter context. The DAX function CALCULATE performs context transition when row context exists. Measures invoked inside row context also undergo context transition as part of measure evaluation.

Interview Question 9: Explain row context and filter context with an example.

Sample Answer: Row context evaluates an expression for a particular row, such as calculating quantity multiplied by unit price. Filter context determines which rows are included in a calculation, such as calculating sales for a selected region and year. Understanding the difference is essential because the same expression can return different results depending on context.

6.4 SUM vs. SUMX

Sales Calculated from Quantity =
SUMX(
    FactSales,
    FactSales[Quantity] * FactSales[UnitPrice]
)

Use SUMX when the required calculation must be evaluated row by row before aggregation. If a reliable precomputed amount column exists and matches the required business definition, summing that column may be simpler and more efficient.

6.5 CALCULATE and CALCULATETABLE

CALCULATE evaluates a scalar expression in a modified filter context. CALCULATETABLE evaluates a table expression in a modified filter context.

Sales for Electronics =
CALCULATE(
    [Total Sales],
    DimProduct[Category] = "Electronics"
)

This measure evaluates total sales with the category filter set to Electronics. The final result also depends on other filters and relationships in the model.

Interview Question 10: Why is CALCULATE important in DAX?

Sample Answer: CALCULATE is one of the most important DAX functions because it evaluates an expression after modifying filter context. It is used for conditional totals, time intelligence, percentage calculations, comparisons, and context transition.

7. Frequently Asked DAX Functions

8. Time Intelligence Interview Questions

8.1 What Is Time Intelligence?

Time intelligence refers to calculations that analyze measures over dates and compare different periods, such as month-to-date, year-to-date, previous month, previous year, and year-over-year growth.

Many standard time-intelligence patterns require a properly configured date table with unique, continuous dates and an appropriate relationship to the fact table. The date table should be marked as a date table where required by the modeling approach.

8.2 Create a Basic Date Table

DimDate =
ADDCOLUMNS(
    CALENDAR(DATE(2022, 1, 1), DATE(2028, 12, 31)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year Month", FORMAT([Date], "YYYY-MM")
)

Choose a date range suitable for the dataset. Sort the month name by Month Number, and ensure Year Month is sorted chronologically when displayed in visuals.

8.3 Year-to-Date Sales

Sales YTD =
TOTALYTD(
    [Total Sales],
    DimDate[Date]
)

This calculates sales from the beginning of the year through the current date in the relevant evaluation context.

8.4 Previous Year Sales

Sales Previous Year =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(DimDate[Date])
)

8.5 Year-over-Year Difference and Growth

Sales YoY Difference =
[Total Sales] - [Sales Previous Year]

Sales YoY Growth % =
DIVIDE(
    [Sales YoY Difference],
    [Sales Previous Year]
)

Format the growth measure as a percentage. When the previous-year value is zero or blank, decide whether a blank or another business-approved result is most appropriate.

Interview Question 11: What is the difference between YTD and MTD?

Sample Answer: Year-to-date calculates values from the start of the year through the current date in context. Month-to-date calculates values from the beginning of the month through the current date in context. Both are useful for monitoring performance against targets and previous periods.

Interview Question 12: Why might a time-intelligence measure return unexpected results?

Sample Answer: I would check whether the date table is valid, whether the relationship to the fact table is correct and active, whether the date column is being used consistently, and whether the current filters produce the intended period. I would also inspect the measure's filter context and consider gaps in dates, fiscal-year settings, and the selected date range.

9. CALCULATE, FILTER, ALL, and REMOVEFILTERS

9.1 Difference Between ALL and REMOVEFILTERS

ALL can return an unfiltered table or column and can also be used as a filter modifier in supported expressions. REMOVEFILTERS is specifically intended to clear filters. Which one is appropriate depends on whether the expression needs an unfiltered table result or simply needs filters removed.

Sales All Products =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(DimProduct)
)

9.2 Percentage of Total Sales

Sales % of All Products =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(DimProduct)
    )
)

When the visual is grouped by product, the numerator represents sales for the current product context. The denominator removes product-table filters while retaining other applicable filters, such as date or region, unless those filters are also removed.

9.3 FILTER with CALCULATE

Sales from Large Orders =
CALCULATE(
    [Total Sales],
    FILTER(
        FactSales,
        FactSales[SalesAmount] > 1000
    )
)

This is useful for illustrating row filtering, but for simple Boolean filter conditions, a direct filter argument to CALCULATE may be clearer and more efficient when it expresses the same requirement.

Interview Question 13: What is the difference between ALL and ALLEXCEPT?

Sample Answer: ALL removes filters from the specified table or column in supported contexts. ALLEXCEPT removes filters from a table except for the specified columns, whose filters are retained. I choose between them based on which filter context the calculation should preserve.

10. Ranking and Top N Analysis

10.1 Rank Products by Sales

Product Sales Rank =
RANKX(
    ALL(DimProduct[ProductName]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

This measure ranks products by sales in descending order. DESC puts the highest value first, while DENSE assigns consecutive ranks after ties. Using ALL on the product name column removes that column's filter, while other model filters can remain active.

10.2 Identify Top Products

Top 5 Product Flag =
IF(
    [Product Sales Rank] <= 5,
    1,
    0
)

Use the flag as a visual filter where suitable. For more complex Top N requirements, such as handling ties or ranking by multiple fields, consider a dedicated Top N filter or a carefully designed ranking expression.

Interview Question 14: What is RANKX used for?

Sample Answer: RANKX evaluates an expression over a table and assigns ranks to the resulting values. I use it for top-selling products, highest-performing salespeople, customer rankings, and other comparative analyses. I pay attention to ties, ascending or descending order, and which filters should be removed during ranking.

11. Filter Functions and Selection Functions

11.1 SELECTEDVALUE

Selected Region =
SELECTEDVALUE(
    DimRegion[RegionName],
    "Multiple or no regions"
)

SELECTEDVALUE returns the column's value when exactly one distinct value is in context. Otherwise, it returns the optional alternate result or BLANK when no alternate result is supplied.

11.2 HASONEVALUE

One Region Selected =
IF(
    HASONEVALUE(DimRegion[RegionName]),
    "Single region",
    "Multiple or no regions"
)

11.3 ISINSCOPE

ISINSCOPE tests whether a column is the current level in a hierarchy or grouping structure. It is useful for measures that need different behavior at product, category, and total levels in a matrix.

Interview Question 15: What is the difference between SELECTEDVALUE and VALUES?

Sample Answer: SELECTEDVALUE returns a scalar value when exactly one distinct value is present, with an optional alternate result otherwise. VALUES returns a table of distinct values from a column or rows from a table, depending on the argument. I use SELECTEDVALUE for scalar logic and VALUES when a table expression is required.

12. Practical Business KPI Measures

Business KPI questions are common in Power BI interviews because they test both DAX knowledge and the ability to translate business requirements into calculations.

12.1 Total Sales

Total Sales =
SUM(FactSales[SalesAmount])

12.2 Total Orders

Total Orders =
DISTINCTCOUNT(FactSales[OrderID])

This assumes OrderID identifies orders consistently. If one order contains multiple rows, DISTINCTCOUNT prevents those rows from being counted as separate orders.

12.3 Average Order Value

Average Order Value =
DIVIDE(
    [Total Sales],
    [Total Orders]
)

12.4 Total Profit and Profit Margin

Total Profit =
[Total Sales] - [Total Cost]

Profit Margin % =
DIVIDE(
    [Total Profit],
    [Total Sales]
)

Confirm whether the business defines profit using cost of goods sold, operating costs, discounts, returns, or other expenses. A technically valid formula can still be wrong if the business definition is unclear.

12.5 Sales Target Achievement

Assume a target measure named [Sales Target] already exists and is correctly filtered by the relevant date, region, and product dimensions.

Target Achievement % =
DIVIDE(
    [Total Sales],
    [Sales Target]
)

Target Variance =
[Total Sales] - [Sales Target]

12.6 Dynamic Status Label

Sales Performance Status =
SWITCH(
    TRUE(),
    [Total Sales] >= [Sales Target], "Target Achieved",
    [Total Sales] >= [Sales Target] * 0.8, "Near Target",
    "Below Target"
)

This assumes the target is meaningful in the current context. If the target is blank, decide how the business wants the status to behave before using the measure in a report.

13. Scenario-Based Power BI Interview Questions

Scenario 1: A Total Sales Card Does Not Change When a Region Slicer Is Selected

Question: What would you investigate?

Sample Answer:

1. Confirm that the slicer uses the intended region dimension.
2. Check that the region dimension has a valid relationship to the sales fact table.
3. Verify that the relationship is active and filter direction supports the expected behavior.
4. Check visual interaction settings to ensure the slicer affects the card.
5. Review the DAX measure for functions that remove region filters.
6. Confirm that the region key values match across the model.

Scenario 2: Sales Totals Are Duplicated After a Merge

Sample Answer: I would check the join keys and cardinality of the merge. If the lookup table contains multiple rows for a key that should be unique, a merge can multiply sales rows. I would validate uniqueness, inspect row counts before and after the merge, and fix the data model rather than hiding the issue with an arbitrary aggregation.

Scenario 3: A Measure Returns the Same Total for Every Product

Sample Answer: I would inspect the measure for ALL, REMOVEFILTERS, or other filter modifications that may remove product context. I would also verify the relationship between the product dimension and fact table and confirm that the visual uses the intended product field.

Scenario 4: A Report Is Slow

Sample Answer: I would identify whether the bottleneck is the source, model, DAX query, visual rendering, or network. I would reduce unnecessary columns and rows, use a star schema, review high-cardinality fields and relationships, optimize expensive measures, reduce unnecessary visuals, and use Performance Analyzer to identify slow visuals.

Scenario 5: Management Wants Current Month Sales Compared with the Previous Month

Sample Answer: I would confirm the date definition, fiscal calendar requirements, and how partial months should be compared. Then I would create a previous-month measure using the date table and compare it with current sales. I would also account for blank or zero prior-period values.

Sales Previous Month =
CALCULATE(
    [Total Sales],
    PREVIOUSMONTH(DimDate[Date])
)

Month-over-Month Difference =
[Total Sales] - [Sales Previous Month]

Month-over-Month Growth % =
DIVIDE(
    [Month-over-Month Difference],
    [Sales Previous Month]
)

These measures depend on a correctly configured date table and the intended date filter context. Partial-period comparisons may require additional logic to compare equivalent numbers of days.

Scenario 6: Two Tables Have a Many-to-Many Relationship

Sample Answer: I would first investigate the business grain and uniqueness of the keys. If possible, I would redesign the model using dimensions and a bridge table. If a many-to-many relationship is necessary, I would define the intended filter flow and test totals carefully to avoid ambiguous filtering or double counting.

14. Power BI Visuals, Filters, and Interactions

14.1 Common Visuals

14.2 Filter Levels

• Visual-level filter: Applies to a particular visual.
• Page-level filter: Applies to visuals on a report page.
• Report-level filter: Applies across report pages.
• Slicer: A visible interactive filter control on the report canvas.

Interview Question 16: What is drill-through?

Sample Answer: Drill-through allows a user to navigate from a summary visual to a detail page filtered for the selected entity or context. For example, a user can select a region in a sales overview and open a detail page showing products, customers, and transactions for that region.

Interview Question 17: What is the difference between drill-down and drill-through?

Sample Answer: Drill-down moves through hierarchy levels within a visual, such as year, quarter, and month. Drill-through navigates to another report page that displays details filtered by the selected data context.

15. Power BI Performance Optimization

15.1 Why Is Performance Important?

Slow reports reduce user adoption and make business analysis less efficient. Performance tuning requires identifying the actual bottleneck rather than applying random changes.

15.2 Best Practices

• Use a well-designed star schema where appropriate.
• Remove unused columns and unnecessary rows before loading data.
• Use appropriate numeric, date, and text data types.
• Avoid unnecessary high-cardinality columns in the model.
• Prefer measures for dynamic aggregations rather than storing redundant calculated values without a reason.
• Use variables to make complex DAX expressions easier to read and maintain.
• Review expensive iterators and filter expressions.
• Limit unnecessary visuals, complex custom visuals, and excessive interactions.
• Use Performance Analyzer to identify slow visuals and inspect generated queries.
• Use external tools such as DAX Studio when available and permitted.
• Review source performance and query folding for data preparation steps.
• Consider aggregations, incremental refresh, and storage mode choices when the dataset and workload justify them.

15.3 DAX Variables

Sales Growth % =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = [Sales Previous Year]
VAR Difference = CurrentSales - PreviousSales
RETURN
    DIVIDE(Difference, PreviousSales)

Variables improve readability by giving intermediate expressions meaningful names. They can also avoid repeated evaluation of an expression in a measure. However, a variable is evaluated in the context where it is defined; it does not automatically recalculate under a later filter change within the same expression.

Interview Question 18: How do you optimize a DAX measure?

Sample Answer: I first measure the problem and inspect the DAX expression and model. Then I simplify unnecessary calculations, review filter context, avoid expensive operations where a simpler expression is equivalent, use appropriate model relationships, and test changes against the original results. I use Performance Analyzer and, when appropriate, DAX Studio to investigate query behavior.

16. Power BI Service, Refresh, and Security

16.1 What Happens After Publishing a Report?

1. Publish the report and semantic model from Power BI Desktop to the appropriate workspace.
2. Configure credentials and data source settings as needed.
3. Configure gateway connectivity for supported on-premises sources.
4. Set up refresh schedules or other refresh mechanisms supported by the model.
5. Configure workspace roles, sharing, app distribution, and data access.
6. Test the report using the intended user permissions and refresh behavior.

16.2 What Is Row-Level Security?

Row-Level Security (RLS) restricts which rows users can see in a semantic model. It can be configured using roles and DAX filter expressions. Dynamic RLS commonly uses a user identity function such as USERPRINCIPALNAME() together with a mapping table that associates users with permitted regions or other entities.

16.3 Example of a Dynamic RLS Pattern

Assume a security mapping table named UserRegionAccess contains user email addresses and region keys, and its relationships are configured to filter the region dimension and sales fact table appropriately.

UserRegionAccess[UserEmail] =
USERPRINCIPALNAME()

The expression above is only a conceptual illustration, not a complete RLS role definition: a DAX measure cannot be used as a security rule in place of a filter expression. A role filter should be defined on an appropriate table, for example:

UserRegionAccess[UserEmail] = USERPRINCIPALNAME()

Test the role in Power BI Desktop and the Service, verify relationship propagation, and assign users or groups to the correct roles. RLS behavior depends on the model, role definitions, and how users access the content.

Interview Question 19: What is the difference between workspace roles and RLS?

Sample Answer: Workspace roles control what users can do with workspace content, such as viewing, editing, or managing it. RLS controls which rows of data a user can access through the semantic model. They solve different security problems and must be configured appropriately.

Interview Question 20: Why can scheduled refresh fail?

Sample Answer: I would check credentials, gateway status, source availability, network access, data source configuration, privacy settings, query errors, and refresh limits or capacity constraints. I would inspect the refresh history and error details to identify the actual failure before changing the model.

17. Common Power BI and DAX Mistakes

18. Hands-On DAX Interview Practice Exercises

Use a sample sales model containing FactSales, DimDate, DimProduct, DimCustomer, and DimRegion. Ensure that keys and relationships are configured before attempting the exercises.

Exercise 1: Basic Measures

• Create a measure for total sales.
• Create a measure for total cost.
• Calculate total profit.
• Calculate profit margin percentage.
• Count distinct orders.

Exercise 2: Filter Context

• Calculate sales for a selected region.
• Calculate sales while removing product filters.
• Calculate the percentage of total sales for each category.
• Explain which filters remain active in each measure.

Exercise 3: Time Intelligence

• Calculate sales year-to-date.
• Calculate previous-year sales.
• Calculate year-over-year difference.
• Calculate year-over-year growth percentage.
• Explain how the date table and relationship affect the results.

Exercise 4: Ranking

• Rank products by total sales.
• Display the top five products.
• Compare dense ranking with skip ranking when ties occur.
• Explain whether the ranking should respect region and date filters.

Exercise 5: Business Scenario

A sales manager wants a report showing total revenue, profit margin, monthly sales trends, top-performing products, regional performance, and target achievement.

1. Identify the required fact and dimension tables.
2. Define the grain of the sales fact table.
3. Build the required relationships.
4. Create the KPI measures.
5. Build an overview page and detailed analysis pages.
6. Add appropriate slicers and drill-through navigation.
7. Validate totals against the source data.
8. Explain performance, refresh, and security decisions.

19. Power BI and DAX Mock Interview Questions with Answers

Question 21: What is the difference between Power Query and DAX?

Answer: Power Query is primarily used to extract, clean, and transform data before it is loaded into the model. DAX is used to define model calculations, measures, calculated columns, and analytical expressions. I use Power Query for data preparation and DAX for calculations that belong in the semantic model.

Question 22: What is the difference between COUNT and COUNTROWS?

Answer: COUNT counts nonblank values in a column for supported data types. COUNTROWS counts rows in a table expression. I use COUNTROWS when I need to count records and COUNT when I specifically need to count nonblank column values.

Question 23: What is the difference between DISTINCTCOUNT and COUNTROWS?

Answer: DISTINCTCOUNT counts unique values in a column, including a blank as a distinct value when present. COUNTROWS counts rows in a table. If an order appears on several sales lines, DISTINCTCOUNT of OrderID is usually more appropriate for counting orders than COUNTROWS of the sales table.

Question 24: What does BLANK mean in DAX?

Answer: BLANK represents a missing or undefined value in DAX. It is not always equivalent to zero or an empty text string. I decide whether a blank should remain blank or be displayed as zero based on the business requirement and the behavior of the visual.

Question 25: What is the difference between IF and SWITCH?

Answer: IF evaluates a logical condition and returns one of two results, with an optional nested structure for more branches. SWITCH is useful when matching an expression to multiple values or when using the pattern SWITCH(TRUE()) for ordered logical conditions. SWITCH often makes multiple business categories easier to read.

Question 26: What is a disconnected table?

Answer: A disconnected table has no model relationship to other tables. It is often used for parameter selection, measure switching, or user-controlled calculation options. A measure reads the selected value and applies the appropriate logic, often through SELECTEDVALUE and SWITCH.

Question 27: What is a dynamic measure selector?

Answer: A dynamic measure selector allows users to choose which metric a visual displays. It can be implemented with a disconnected selection table and a measure that returns the selected metric, or with supported field parameters when those fit the use case.

Selected Metric =
SWITCH(
    SELECTEDVALUE(MetricSelector[Metric]),
    "Sales", [Total Sales],
    "Profit", [Total Profit],
    "Orders", [Total Orders],
    BLANK()
)

Question 28: How would you validate a DAX measure?

Answer: I would compare the measure with a trusted source or independently calculated result, test it at different levels of aggregation, apply representative slicers, inspect totals and subtotals, and check blank or zero cases. I would also verify that the model relationships and business definitions match the calculation.

Question 29: What is an aggregation table?

Answer: An aggregation table stores data at a summarized grain to support faster queries for suitable analysis patterns. Depending on the model design and storage configuration, Power BI may use aggregations to answer queries without scanning the full detail data. The aggregation must preserve the information required by the intended calculations.

Question 30: How do you explain a complex DAX measure to a nontechnical stakeholder?

Answer: I start with the business question, explain the inputs and business rules, and describe how filters affect the result. I then validate the result with a simple example and clarify important assumptions, such as whether growth is compared with the previous month, previous year, or a target.

20. How to Explain Your Power BI Project in an Interview

A project discussion helps interviewers assess your practical skills, decision-making, and ability to connect technical work with business outcomes. Prepare a concise explanation that you can expand when the interviewer asks follow-up questions.

20.1 Recommended Project Explanation Structure

1. Business problem: Explain what the organization needed to understand or improve.
2. Data sources: Describe the files, databases, or systems used.
3. Data preparation: Explain important cleaning and transformation steps in Power Query.
4. Data model: Describe the fact tables, dimension tables, relationships, and grain.
5. DAX calculations: Explain key measures and the business rules behind them.
6. Report design: Describe the pages, visuals, slicers, and navigation.
7. Validation: Explain how you checked totals and calculation accuracy.
8. Optimization: Describe measured performance improvements, if any.
9. Deployment: Explain publishing, refresh, permissions, and security.
10. Business impact: State measurable outcomes only when you have evidence for them.

20.2 Sample Project Introduction

Sample Answer: “I developed a Power BI sales analysis report to help stakeholders monitor revenue, profit, order volume, product performance, and regional trends. I used Power Query to clean and standardize the source data, designed a star schema with a sales fact table and supporting dimensions, and created DAX measures for core KPIs and period comparisons. I built interactive report pages with slicers and drill-through navigation, validated the results against source totals, and prepared the report for publishing and controlled access.”

Adapt this answer to your own project. Do not claim tools, results, or responsibilities that you did not personally use or perform.

21. A Seven-Day Interview Preparation Plan

22. Interview Readiness Checklist

• Can I explain the difference between Power BI Desktop and Power BI Service?
• Can I explain Power Query, M, and query folding?
• Can I distinguish fact tables from dimension tables?
• Can I design and explain a star schema?
• Can I explain relationship cardinality and filter direction?
• Can I distinguish measures, calculated columns, and calculated tables?
• Can I explain row context, filter context, and context transition?
• Can I use SUM, SUMX, CALCULATE, FILTER, ALL, and DIVIDE correctly?
• Can I calculate YTD, previous-period sales, and growth percentages?
• Can I explain RANKX, SELECTEDVALUE, and filter-removal behavior?
• Can I debug incorrect totals and measures that ignore slicers?
• Can I describe how I validate calculations and optimize performance?
• Can I explain refresh configuration, gateway connectivity, and RLS?
• Can I present a real project and explain my personal contribution?

23. Final Revision Summary

• Power Query: Used for extracting, cleaning, and transforming data.
• DAX: Used for model calculations and analytical expressions.
• Measure: Evaluated in the current query and filter context.
• Calculated column: Evaluated row by row and stored in the model.
• SUM: Aggregates a numeric column.
• SUMX: Iterates over a table, evaluates an expression per row, and sums the results.
• CALCULATE: Evaluates an expression in modified filter context.
• FILTER: Returns rows that satisfy a condition.
• ALL / REMOVEFILTERS: Used to remove filters in supported calculation contexts.
• DIVIDE: Performs division with optional alternate-result handling.
• RANKX: Ranks values over a table expression.
• Time intelligence: Requires a suitable date model and correct date context.
• Star schema: Separates transactional facts from descriptive dimensions.
• Performance optimization: Starts with measurement, model review, and query analysis.
• RLS: Restricts which data rows users can access.
• Interview success: Combine clear explanations, correct DAX, practical examples, and verified project experience.

24. Conclusion

Practicing Power BI and DAX interview questions helps candidates build the technical knowledge and problem-solving skills required for business intelligence roles. Strong preparation includes understanding data preparation, modeling, DAX context, time intelligence, report interactions, performance optimization, and deployment.

Do not memorize formulas without understanding them. Practice each measure in a working Power BI model, test how slicers affect results, compare totals against trusted data, and explain the business purpose of each calculation. This approach prepares you for both theoretical questions and practical interview exercises.

Explore the training: Microsoft Power BI Training at JustAcademy

Book a demo: Register for a Power BI Course Demo

Visit the website: JustAcademy